use sqlx::{PgExecutor, Postgres, QueryBuilder, Result};

use crate::{filter, model, utils::id};

pub async fn is_exists<'a>(e: impl PgExecutor<'a>, email: &str, id: Option<&str>) -> Result<bool> {
    let mut q = QueryBuilder::new("SELECT COUNT(*) FROM users WHERE email=");
    q.push_bind(email);

    if let Some(id) = id {
        q.push(" AND id<>").push_bind(id);
    }

    let count: (i64,) = q.build_query_as().fetch_one(e).await?;

    Ok(count.0 > 0)
}

pub async fn create<'a>(e: impl PgExecutor<'a>, m: &model::user::User) -> Result<String> {
    let id = id::new();

    let mut q = QueryBuilder::new(
        r#"INSERT INTO "users" ("id","email","password","status","role","dateline") "#,
    );
    q.push_values(&[m], |mut b, u| {
        b.push_bind(&id)
            .push_bind(&u.email)
            .push_bind(&u.password)
            .push_bind(&u.status)
            .push_bind(&u.role)
            .push_bind(&u.dateline);
    });

    q.build().execute(e).await?;

    Ok(id)
}

pub async fn update<'a>(
    e: impl PgExecutor<'a>,
    m: &model::user::User,
    skip_update_pwd: bool,
) -> Result<u64> {
    let mut q = QueryBuilder::new(r#"UPDATE "users" SET "email"= "#);
    q.push_bind(&m.email);

    if !skip_update_pwd {
        q.push(r#", "password" = "#).push_bind(&m.password);
    }

    q.push(" WHERE id=").push_bind(&m.id);

    let aff = q.build().execute(e).await?.rows_affected();
    Ok(aff)
}

pub async fn update_password<'a>(e: impl PgExecutor<'a>, id: &str, password: &str) -> Result<u64> {
    let q = sqlx::query("UPDATE users SET password=$1 WHERE id=$2")
        .bind(password)
        .bind(id);
    let aff = q.execute(e).await?.rows_affected();
    Ok(aff)
}

pub async fn update_status<'a>(
    e: impl PgExecutor<'a>,
    id: &str,
    status: &model::user::Status,
) -> Result<u64> {
    let q = sqlx::query("UPDATE users SET status=$1 WHERE id=$2")
        .bind(status)
        .bind(id);
    let aff = q.execute(e).await?.rows_affected();
    Ok(aff)
}

pub async fn del<'a>(e: impl PgExecutor<'a>, id: &str) -> Result<u64> {
    super::del(e, "users", id).await
}

pub async fn find<'a>(
    e: impl PgExecutor<'a>,
    f: &filter::user::Find<'a>,
) -> Result<Option<model::user::User>> {
    let mut q = QueryBuilder::new(
        r#"SELECT "id","email","password","status","role","dateline" FROM "users" WHERE 1=1"#,
    );

    match &f.by {
        &filter::user::FindBy::ID(id) => q.push(" AND id=").push_bind(id),
        &filter::user::FindBy::Email(email) => q.push(" AND email=").push_bind(email),
    };

    if let Some(status) = &f.status {
        q.push(r#" AND "status"="#).push_bind(status);
    }

    if let Some(role) = &f.role {
        q.push(r#" AND "role"="#).push_bind(role);
    }

    q.build_query_as().fetch_optional(e).await
}

pub async fn list_data<'a>(
    e: impl PgExecutor<'a>,
    f: &filter::user::List<'a>,
) -> Result<Vec<model::user::User>> {
    let mut q = QueryBuilder::new(
        r#"SELECT "id","email","password","status","role","dateline" FROM "users" WHERE 1=1"#,
    );
    list_query_builder(&mut q, f);

    let order = match f.order {
        Some(order) => order,
        None => "id DESC",
    };
    q.push(" ORDER BY ").push_bind(order);

    q.push(" LIMIT ")
        .push_bind(f.pagination.page_size())
        .push(" OFFSET ")
        .push_bind(f.pagination.offset());

    q.build_query_as().fetch_all(e).await
}

pub async fn list_count<'a>(e: impl PgExecutor<'a>, f: &filter::user::List<'a>) -> Result<u32> {
    let mut q = QueryBuilder::new(r#"SELECT COUNT(*) FROM "users" WHERE 1=1"#);

    list_query_builder(&mut q, f);

    let count: (i64,) = q.build_query_as().fetch_one(e).await?;

    Ok(count.0 as u32)
}

fn list_query_builder<'a>(q: &mut QueryBuilder<'a, Postgres>, f: &filter::user::List<'a>) {
    if let Some(email) = f.email {
        let email = format!("%{email}%");
        q.push(" AND email ILIKE ").push_bind(email);
    }

    if let Some(status) = f.status {
        q.push(r#" AND "status" = "#).push_bind(status);
    }
    if let Some(role) = f.role {
        q.push(r#" AND "role" = "#).push_bind(role);
    }
}

#[cfg(test)]
mod test {
    use chrono::Local;
    use sqlx::{postgres::PgPoolOptions, PgPool, Result};

    use crate::{model, utils};

    async fn get_pool() -> Result<PgPool> {
        let dsn = std::env::var("SHORT_URL_DB")
            .unwrap_or("postgres://short_url:short_url@127.0.0.1:5432/short_url".into());
        PgPoolOptions::new().max_connections(1).connect(&dsn).await
    }

    #[tokio::test]
    async fn test_user_create_admin() {
        let pool = get_pool().await.unwrap();

        let password = utils::password::hash("china.fr.mu").unwrap();
        let email = "admin@china.fr.mu".to_string();
        let status = model::user::Status::Actived;
        let role = model::user::Role::Admin;
        let dateline = Local::now();

        let m = model::user::User {
            email,
            password,
            status,
            role,
            dateline,
            ..Default::default()
        };

        super::create(&pool, &m).await.unwrap();
    }
    #[tokio::test]
    async fn test_user_create_member() {
        let pool = get_pool().await.unwrap();

        let password = utils::password::hash("china.fr.mu").unwrap();
        let email = "user@china.fr.mu".to_string();
        let status = model::user::Status::Actived;
        let role = model::user::Role::Member;
        let dateline = Local::now();

        let m = model::user::User {
            email,
            password,
            status,
            role,
            dateline,
            ..Default::default()
        };

        super::create(&pool, &m).await.unwrap();
    }
}
